/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50725
 Source Host           : localhost:3306
 Source Schema         : biquge

 Target Server Type    : MySQL
 Target Server Version : 50725
 File Encoding         : 65001

 Date: 14/02/2019 10:48:30
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for authors
-- ----------------------------
DROP TABLE IF EXISTS `authors`;
CREATE TABLE `authors` (
  `authorid` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `authorname` varchar(50) NOT NULL COMMENT '作者笔名,可能会重复',
  `authordescription` varchar(255) DEFAULT NULL COMMENT '作者简介',
  `datastatus` smallint(1) NOT NULL DEFAULT '0' COMMENT '数据状态,0为可用,1为禁用',
  PRIMARY KEY (`authorid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='作者表';

-- ----------------------------
-- Records of authors
-- ----------------------------
BEGIN;
INSERT INTO `authors` VALUES (1, '辰东', '介绍1,编写了xxxx', 0);
INSERT INTO `authors` VALUES (2, '唐家三少', '介绍1,编写了xxxx', 0);
INSERT INTO `authors` VALUES (3, '烽火戏诸侯', '介绍1,编写了xxxx', 0);
INSERT INTO `authors` VALUES (4, '天蚕土豆', '介绍1,编写了xxxx', 0);
INSERT INTO `authors` VALUES (5, '孤焚', '介绍1,编写了xxxx', 0);
COMMIT;

-- ----------------------------
-- Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
  `bookid` int(11) NOT NULL AUTO_INCREMENT COMMENT '图书主键',
  `bookname` varchar(50) NOT NULL COMMENT '书名,可能会重复',
  `categoryid` int(11) DEFAULT NULL COMMENT '分类主键,此处是外键',
  `bookstatus` smallint(6) NOT NULL DEFAULT '0' COMMENT '连载状态,0为连载,1为完结',
  `authorid` int(11) DEFAULT NULL COMMENT '作者主键,此处是外键',
  `booktotalstars` double NOT NULL DEFAULT '0' COMMENT '总共被评价的星数',
  `booktotalstartimes` int(11) NOT NULL DEFAULT '0' COMMENT '总共被评价的次数',
  `bookaveragestar` double NOT NULL DEFAULT '0' COMMENT '当前星级,通过总星数,除以总次数获取',
  `bookdescription` varchar(255) DEFAULT NULL COMMENT '图书简介',
  `shelftime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上架时间',
  `readernum` int(11) NOT NULL DEFAULT '0' COMMENT '阅读数量,必须是点击阅读后增加',
  `favnum` int(11) NOT NULL DEFAULT '0' COMMENT '收藏数量,收藏后添加',
  `clicksnum` int(11) NOT NULL DEFAULT '0' COMMENT '点击数量,用户点击图书进入详情后增加',
  `datastatus` smallint(1) NOT NULL DEFAULT '0' COMMENT '数据状态0为正常,1为禁用',
  `bookpicurl` varchar(255) DEFAULT NULL COMMENT '图书图片地址',
  PRIMARY KEY (`bookid`) USING BTREE,
  KEY `fenleiwaijian` (`categoryid`) USING BTREE,
  KEY `zuozhewaijian` (`authorid`) USING BTREE,
  CONSTRAINT `fenleiwaijian` FOREIGN KEY (`categoryid`) REFERENCES `categories` (`categoryid`),
  CONSTRAINT `zuozhewaijian` FOREIGN KEY (`authorid`) REFERENCES `authors` (`authorid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='图书表';

-- ----------------------------
-- Records of books
-- ----------------------------
BEGIN;
INSERT INTO `books` VALUES (1, '永生天', 1, 0, 5, 0, 0, 0, '永生之难,难于上九天', '2019-02-13 12:22:23', 0, 0, 0, 0, 'http://img.biquyun.com/image/8/8923/8923s.jpg');
INSERT INTO `books` VALUES (2, '遮天', 1, 0, 1, 0, 0, 0, '九龙拉棺', '2019-02-13 12:23:10', 0, 0, 0, 0, 'http://img.biquyun.com/image/2/2016/2016s.jpg\n');
INSERT INTO `books` VALUES (3, '大主宰', 2, 0, 4, 0, 0, 0, '大千世界,位面交汇', '2019-02-13 23:50:14', 0, 0, 0, 0, 'http://img.biquyun.com/image/0/757/757s.jpg');
INSERT INTO `books` VALUES (4, '武动乾坤', 2, 0, 4, 0, 0, 0, '武动乾坤', '2019-02-13 23:51:03', 0, 0, 0, 0, 'http://img.biquyun.com/image/1/1872/1872s.jpg');
COMMIT;

-- ----------------------------
-- Table structure for bookshelfs
-- ----------------------------
DROP TABLE IF EXISTS `bookshelfs`;
CREATE TABLE `bookshelfs` (
  `bookid` int(11) NOT NULL COMMENT '图书主键',
  `userid` int(11) NOT NULL COMMENT '用户主键',
  `adddate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  KEY `tushuidwaijian` (`bookid`) USING BTREE,
  KEY `yonghuidwaijian` (`userid`) USING BTREE,
  CONSTRAINT `tushuidwaijian` FOREIGN KEY (`bookid`) REFERENCES `books` (`bookid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `yonghuidwaijian` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='书架表';

-- ----------------------------
-- Table structure for categories
-- ----------------------------
DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `categoryid` int(11) NOT NULL AUTO_INCREMENT COMMENT '图书分类主键',
  `categoryname` varchar(50) NOT NULL COMMENT '图书分类名称',
  `datastatus` smallint(1) NOT NULL DEFAULT '0' COMMENT '数据状态0为正常,1为禁用',
  PRIMARY KEY (`categoryid`) USING BTREE,
  UNIQUE KEY `categoties_categoryname_uindex` (`categoryname`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='图书分类表';

-- ----------------------------
-- Records of categories
-- ----------------------------
BEGIN;
INSERT INTO `categories` VALUES (1, '武侠仙侠', 0);
INSERT INTO `categories` VALUES (2, '玄幻奇幻', 0);
INSERT INTO `categories` VALUES (3, '都市言情', 0);
INSERT INTO `categories` VALUES (4, '历史军事', 0);
INSERT INTO `categories` VALUES (5, '科幻灵异', 0);
INSERT INTO `categories` VALUES (6, '网游竞技', 0);
INSERT INTO `categories` VALUES (7, '女生频道', 0);
INSERT INTO `categories` VALUES (8, '同人小说', 0);
COMMIT;

-- ----------------------------
-- Table structure for chapters
-- ----------------------------
DROP TABLE IF EXISTS `chapters`;
CREATE TABLE `chapters` (
  `chapterid` int(11) NOT NULL AUTO_INCREMENT COMMENT '章节主键',
  `bookid` int(11) DEFAULT NULL COMMENT '所属图书外键',
  `chaptercontent` longtext COMMENT '章节内容',
  `chaptertitle` varchar(255) NOT NULL COMMENT '章节标题',
  `updatetime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `datastatus` smallint(1) NOT NULL DEFAULT '0' COMMENT '数据状态0为正常,1为禁用',
  PRIMARY KEY (`chapterid`) USING BTREE,
  KEY `tushuwaijian` (`bookid`) USING BTREE,
  CONSTRAINT `tushuwaijian` FOREIGN KEY (`bookid`) REFERENCES `books` (`bookid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='章节表';

-- ----------------------------
-- Table structure for comments
-- ----------------------------
DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments` (
  `commentid` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `parentcommentid` int(11) DEFAULT NULL COMMENT '父评论 id,没有则为空',
  `commenttext` varchar(255) NOT NULL COMMENT '评论内容,此处长度限制为255',
  `userid` int(11) NOT NULL COMMENT '评论用户 id,此处是外键',
  `bookid` int(11) NOT NULL COMMENT '被评论的书籍 id,此处是外键',
  `commentdate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '评论发布时间',
  `likecount` int(11) NOT NULL DEFAULT '0' COMMENT '点赞数量',
  `datastatus` smallint(1) NOT NULL DEFAULT '0' COMMENT '数据状态0为正常,1为禁用',
  PRIMARY KEY (`commentid`) USING BTREE,
  KEY `faburenid` (`userid`) USING BTREE,
  KEY `tushuid` (`bookid`) USING BTREE,
  CONSTRAINT `faburenid` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`),
  CONSTRAINT `tushuid` FOREIGN KEY (`bookid`) REFERENCES `books` (`bookid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='评论表';

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `userid` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` char(32) NOT NULL COMMENT '密码,MD5值',
  `passwordsalt` char(10) NOT NULL COMMENT '密码的盐值,固定长度10',
  `registrydate` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  `lastlogindate` datetime DEFAULT NULL COMMENT '上次登录时间',
  `currentlogindate` datetime DEFAULT NULL COMMENT '本次登录时间',
  `datastatus` smallint(1) NOT NULL DEFAULT '0' COMMENT '数据状态0为正常,1为禁用',
  PRIMARY KEY (`userid`) USING BTREE,
  UNIQUE KEY `users_username_uindex` (`username`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户表';

SET FOREIGN_KEY_CHECKS = 1;
